﻿CREATE VIEW [dbo].[YardPriceListExcelOutput_View]
AS
SELECT     TOP (100) PERCENT dbo.GenBoatData_Tbl.BoatGenID,
                          (SELECT     CASE WHEN dbo.GenBoatData_Tbl.BoatRange IS NULL 
                                                   THEN '' ELSE dbo.GenBoatData_Tbl.boatRange END + ' ' + CASE WHEN dbo.GenBoatData_Tbl.BoatModel IS NULL 
                                                   THEN '' ELSE dbo.GenBoatData_Tbl.boatModel END + ' ' + CASE WHEN dbo.GenBoatData_Tbl.BoatType IS NULL 
                                                   THEN '' ELSE dbo.GenBoatData_Tbl.boatType END AS Expr1) AS BoatMake, dbo.GenBoatData_Tbl.BoatModel, 
                      dbo.QuoteSailType_Tbl.QuoteSailTypeID AS Expr2, dbo.QuoteSailType_Tbl.GenSailTypeID AS Expr1, dbo.GenBoatData_Tbl.Loa, 
                      dbo.GenBoatData_Tbl.I, dbo.GenBoatData_Tbl.J, dbo.GenBoatData_Tbl.P, dbo.GenBoatData_Tbl.E, dbo.GenBoatData_Tbl.DesignYear, 
                      dbo.OEMSailList_Tbl.OEMSailListID, dbo.OEMSailList_Tbl.BoatGenID AS Expr3, dbo.OEMSailList_Tbl.OEMIdentifier, dbo.OEMSailList_Tbl.CustomerID, 
                      dbo.OEMSailList_Tbl.MarketID, dbo.OEMSailList_Tbl.QuoteSailTypeID, dbo.OEMSailList_Tbl.Area, dbo.OEMSailList_Tbl.GenericYarnID, 
                      dbo.OEMSailList_Tbl.FilmTypeID, dbo.OEMSailList_Tbl.DPI, dbo.OEMSailList_Tbl.CurrencyID, dbo.OEMSailList_Tbl.TierID, 
                      dbo.OEMSailList_Tbl.UsageID, dbo.OEMSailList_Tbl.Createdate, dbo.OEMSailList_Tbl.ConstructionID, dbo.OEMSailList_Tbl.MarketRetailPrice, 
                      dbo.OEMSailList_Tbl.ListRetailPrice, dbo.OEMSailList_Tbl.OEMRetailPrice, dbo.OEMSailList_Tbl.Cut, dbo.OEMSailList_Tbl.Weight, 
                      dbo.OEMSailList_Tbl.AccessoryID, dbo.OEMSailList_Tbl.SailNumberColourID, dbo.OEMSailList_Tbl.AccessoryColourID, 
                      dbo.OEMSailList_Tbl.NylonColourDetails, dbo.OEMSailList_Tbl.FabricID, dbo.OEMSailList_Tbl.NotionalArea, dbo.OEMSailList_Tbl.TopSurfaceID, 
                      dbo.OEMSailList_Tbl.BotSurfaceID, dbo.OEMSailList_Tbl.GenSailTypeID, dbo.OEMSailList_Tbl.Notes, dbo.OEMSailList_Tbl.OptionID, 
                      dbo.OEMSailList_Tbl.DesignFile, dbo.OEMSailList_Tbl.Standard, dbo.OEMSailList_Tbl.QuoteLineItemID, dbo.OEMSailList_Tbl.QuoteID, 
                      dbo.OEMSailList_Tbl.LineItem, dbo.OEMSailList_Tbl.SailModelCode, dbo.QuoteSailType_Tbl.SailType AS SailTypeText, 
                      dbo.Construction_Tbl.Construction AS ConstructionText, dbo.Tier_Tbl.Tier AS TierText, dbo.Tier_Tbl.TierPriceIndicator AS TierFactor, 
                      dbo.QuoteLineItem_Tbl.Luff, dbo.QuoteLineItem_Tbl.LPorFoot,
                          (SELECT     PartCode
                            FROM          dbo.Fabric_Tbl
                            WHERE      (FabricID = dbo.OEMSailList_Tbl.FabricID)) AS Partcode, dbo.GenericYarn_Tbl.YarnType AS YarnText, dbo.Currency_Tbl.CurrencySymbol, 
                      dbo.Currency_Tbl.ExRate
FROM         dbo.GenBoatData_Tbl INNER JOIN
                      dbo.OEMSailList_Tbl ON dbo.GenBoatData_Tbl.BoatGenID = dbo.OEMSailList_Tbl.BoatGenID INNER JOIN
                      dbo.QuoteSailType_Tbl ON dbo.OEMSailList_Tbl.QuoteSailTypeID = dbo.QuoteSailType_Tbl.QuoteSailTypeID INNER JOIN
                      dbo.Construction_Tbl ON dbo.OEMSailList_Tbl.ConstructionID = dbo.Construction_Tbl.ConstructionID INNER JOIN
                      dbo.Tier_Tbl ON dbo.OEMSailList_Tbl.TierID = dbo.Tier_Tbl.TierID INNER JOIN
                      dbo.GenericYarn_Tbl ON dbo.OEMSailList_Tbl.GenericYarnID = dbo.GenericYarn_Tbl.GenericYarnID INNER JOIN
                      dbo.QuoteLineItem_Tbl ON dbo.OEMSailList_Tbl.QuoteLineItemID = dbo.QuoteLineItem_Tbl.QuoteLineItemID INNER JOIN
                      dbo.Currency_Tbl ON dbo.OEMSailList_Tbl.CurrencyID = dbo.Currency_Tbl.CurrencyID
ORDER BY dbo.GenBoatData_Tbl.BoatGenID, Expr1, Expr2